﻿using System;
using System.Data;
using System.Data.OleDb;

namespace ShopB2C.DBUtilities.OleDbHelper
{
    /// <summary>
    /// 存储过程执行
    /// <remarks>
    /// MatchZhou - 2009/1/20
    /// </remarks>
    /// </summary>
    public class OleDbProcedure : DbBase
    {

        #region 存储过程

        /// <summary>
        /// 执行存储过程,返回指定的执行标志
        /// </summary>
        /// <param name="procedure">存储过程名称</param>
        /// <param name="sqlParams">sql 参数组</param>
        /// <returns>执行标志</returns>
        public static int ExecuteNonQuery(string procedure, params OleDbParameter[] sqlParams)
        {
            using (OleDbConnection conn = new OleDbConnection(DbBase.ConnectionString))
            {
                using (OleDbCommand cmd = new OleDbCommand(procedure, conn))
                {
                    try
                    {
                        conn.Open();
                        cmd.CommandType = CommandType.StoredProcedure;
                        DbBase.PrepareCommand(cmd, sqlParams);
                        return cmd.ExecuteNonQuery();
                    }
                    catch (OleDbException e)
                    {
                        conn.Close();
                        throw e;
                    }
                }
            }
        }
        /// <summary>
        /// 执行存储过程,返回查询结果
        /// </summary>
        /// <param name="procedure">存储过程名称</param>
        /// <param name="sqlParams">sql 参数</param>
        /// <returns>查询结果</returns>
        public static object ExecuteScalar(string procedure, params OleDbParameter[] sqlParams)
        {
            using (OleDbConnection conn = new OleDbConnection(DbBase.ConnectionString))
            {
                using (OleDbCommand cmd = new OleDbCommand(procedure, conn))
                {
                    try
                    {
                        conn.Open();
                        cmd.CommandType = CommandType.StoredProcedure;
                        DbBase.PrepareCommand(cmd, sqlParams);
                        object obj = cmd.ExecuteScalar();
                        if (Object.Equals(obj, null) || Object.Equals(obj, DBNull.Value))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (OleDbException e)
                    {
                        conn.Close();
                        throw e;
                    }
                }
            }
        }
        /// <summary>
        /// 执行存储过程，返回 OleDbDataReader，使用后手动释放资源
        /// </summary>
        /// <param name="procedure">存储过程名称</param>
        /// <param name="sqlParams">sql 参数</param>
        /// <returns>OleDbDataReader</returns>
        public static OleDbDataReader ExecuteReader(string procedure, params OleDbParameter[] sqlParams)
        {
            OleDbConnection conn = new OleDbConnection(DbBase.ConnectionString);
            OleDbCommand cmd = new OleDbCommand(procedure, conn);
            try
            {
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                DbBase.PrepareCommand(cmd, sqlParams);
                OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
            }
            catch (OleDbException e)
            {
                if (conn.State != ConnectionState.Closed) conn.Close();
                throw e;
            }
        }
        /// <summary>
        /// 执行存储过程,返回 DataTable
        /// </summary>
        /// <param name="procedure">存储过程名称</param>
        /// <param name="sqlParams">sql 参数</param>
        /// <returns>DataTable</returns>
        public static DataTable QueryDataTable(string procedure, params OleDbParameter[] sqlParams)
        {
            return OleDbProcedure.QueryDataSet(procedure, sqlParams).Tables[0];
        }
        /// <summary>
        /// 执行查询语句,返回 DataSet
        /// </summary>
        /// <param name="procedure">存储过程名称</param>
        /// <param name="sqlParams">sql 参数</param>
        /// <returns>DataSet</returns>
        public static DataSet QueryDataSet(string procedure, params OleDbParameter[] sqlParams)
        {
            using (OleDbConnection conn = new OleDbConnection(DbBase.ConnectionString))
            {
                using (OleDbCommand cmd = new OleDbCommand(procedure, conn))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        conn.Open();
                        cmd.CommandType = CommandType.StoredProcedure;
                        DbBase.PrepareCommand(cmd, sqlParams);
                        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                        da.Fill(ds);
                    }
                    catch (OleDbException e)
                    {
                        throw e;
                    }
                    finally
                    {
                        conn.Close();
                    }
                    return ds;
                }
            }
        }

        #endregion

    }
}